package biz.bbtec.sbm.issuetracking.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.opensymphony.xwork2.ActionContext;

import biz.bbtec.sbm.db.dbConnection;
import biz.bbtec.sbm.issuetracking.bean.AttachmentBean;
import biz.bbtec.sbm.issuetracking.bean.NoteBean;
import biz.bbtec.sbm.issuetracking.bean.WorkBean;
import biz.bbtec.sbm.user.bean.UserBean;

public class AttachmentService {

	dbConnection db = new dbConnection();

	public List ShowAllAttachemnt(NoteBean note) {
		int attnote = 0;

		if (note == null) {
			note = new NoteBean();
			Map session = ActionContext.getContext().getSession();
			attnote = (Integer) session.get("attnote");
			note.setNoteid(attnote);
			session.remove("attnote");
		} else {
			attnote = note.getNoteid();
		}

		List<AttachmentBean> list = new ArrayList();

		String sql = "select * from attachment where noteid = " + "'" + attnote
				+ "'";

		Map session = ActionContext.getContext().getSession();
		UserBean user = (UserBean) session.get("user");
		int judge = 0;
		String sql2 = "update noteaccess set past = ? where noteid = " + "'"
				+ attnote + "'" + " and userid = " + "'" + user.getUserid()
				+ "'";

		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			ResultSet rs = past.executeQuery();
			while (rs.next()) {
				AttachmentBean attachment = new AttachmentBean();
				attachment.setAttid(Integer.parseInt(rs.getString("attid")));
				attachment.setDate(rs.getString("date"));
				attachment.setTitle(rs.getString("title"));
				attachment.setNoteid(note.getNoteid());

				list.add(attachment);
			}
			rs.close();
			past.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		try {
			past = conn.prepareStatement(sql2);
			past.setInt(1, judge);
			past.executeUpdate();
			past.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public List ShowAllAtt(WorkBean work) {

		List<AttachmentBean> list = new ArrayList();

		Map session = ActionContext.getContext().getSession();
		UserBean user = (UserBean) session.get("user");
		String sql = "select * from attachment where wpid = " + "'"
				+ work.getWpid() + "'";
		String sql2 = "update workaccess set past = '0' where wpid =" + "'"
				+ work.getWpid() + "'" + " and userid = " + "'"
				+ user.getUserid() + "'";

		Connection conn = db.getConnection();
		try {
			PreparedStatement past = conn.prepareStatement(sql);
			ResultSet rs = past.executeQuery();
			while (rs.next()) {
				AttachmentBean att = new AttachmentBean();
				att.setAttid(Integer.parseInt(rs.getString("attid")));
				att.setWpid(Integer.parseInt(rs.getString("wpid")));
				att.setTitle(rs.getString("title"));
				att.setDate(rs.getString("date"));
				att.setTaskid(Integer.parseInt(rs.getString("taskid")));
				list.add(att);
			}
			rs.close();
			past = conn.prepareStatement(sql2);
			past.executeUpdate();
			past.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public boolean AddAttachment(String filename, AttachmentBean attachment)
			throws ParseException, SQLException {

		Map session = ActionContext.getContext().getSession();
		int attnoteid = (Integer) session.get("attnote");

		boolean flag = false;

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String thisDate = sdf.format(date);

		Connection conn = db.getConnection();

		try {
			PreparedStatement past = null;
			String sql = "select wpid,taskid from note where noteid = " + "'"
					+ attnoteid + "'";

			past = conn.prepareStatement(sql);
			ResultSet rs = past.executeQuery();
			if (rs.next()) {
				int wpid = rs.getInt(1);
				int taskid = rs.getInt(2);
				rs.close();
				past.close();
				String sql1 = "insert into attachment(noteid,title,date,wpid,taskid) values ("
						+ "'"
						+ attnoteid
						+ "'"
						+ ","
						+ "'"
						+ filename
						+ "'"
						+ ","
						+ "'"
						+ thisDate
						+ "'"
						+ ","
						+ "'"
						+ wpid
						+ "'"
						+ "," + "'" + taskid + "'" + ")";
				past = conn.prepareStatement(sql1);
				past.executeUpdate(sql1);
				past.close();
			} else {
				String sql1 = "insert into attachment(noteid,title,date,wpid,taskid) values ("
						+ "'"
						+ attnoteid
						+ "'"
						+ ","
						+ "'"
						+ filename
						+ "'"
						+ "," + "'" + thisDate + "'" + ")";
				past = conn.prepareStatement(sql1);
				past.executeUpdate(sql1);
				past.close();
			}
			flag = true;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public String ExitAttachment(AttachmentBean attachment) throws SQLException {
		String title = null;
		Connection conn = db.getConnection();

		String sql = "select title from attachment where attid =" + "'"
				+ attachment.getAttid() + "'";

		try {
			PreparedStatement past = conn.prepareStatement(sql);
			ResultSet rs = past.executeQuery();
			if (rs.next()) {
				title = rs.getString("title");
			}
			rs.close();
			past.close();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return title;
	}

	public String ExitAtt(AttachmentBean attachment) throws SQLException {
		String title = null;
		Connection conn = db.getConnection();

		String sql = "select title from attachment where attid =" + "'"
				+ attachment.getAttid() + "'";

		try {
			PreparedStatement past = conn.prepareStatement(sql);
			ResultSet rs = past.executeQuery();
			if (rs.next()) {
				title = rs.getString("title");
			}
			rs.close();
			past.close();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return title;
	}

	public boolean DeleteAttachment(AttachmentBean attachment)
			throws SQLException {
		boolean flag = false;

		Connection conn = db.getConnection();
		String sql = "delete from attachment where attid =" + "'"
				+ attachment.getAttid() + "'";
		try {
			PreparedStatement past = null;
			past = conn.prepareStatement(sql);
			past.executeUpdate(sql);
			flag = true;
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public boolean DeleteAtt(AttachmentBean attachment) throws SQLException {
		boolean flag = false;

		Connection conn = db.getConnection();
		String sql = "delete from attachment where attid =" + "'"
				+ attachment.getAttid() + "'";
		try {
			PreparedStatement past = null;
			past = conn.prepareStatement(sql);
			past.executeUpdate(sql);
			flag = true;
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public String Download(AttachmentBean attachment) throws SQLException {
		String filename = null;
		Connection conn = db.getConnection();
		String sql = "select title from attachment where attid =" + "'"
				+ attachment.getAttid() + "'";

		try {
			PreparedStatement past = conn.prepareStatement(sql);
			ResultSet rs = past.executeQuery();
			if (rs.next()) {
				filename = rs.getString("title");
			}
			rs.close();
			past.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return filename;
	}
}
